﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Irony.Parsing;
using System.Runtime.Serialization;
using System.Runtime.Serialization.Formatters.Binary;
using System.IO;
using System.Xml.Linq;

namespace Converter.SqlXml
{
    public class SqlXmlColumn
    {
        public string columnName { get; set; }
        public string value { get; set; }
        public Dictionary<string, string> columnInfo { get; set; }
    }

    public interface ISqlXmlConverter
    {
        bool BuildSqlXmlPattern(ParseTree parseTree);
        string WritePattern();
        List<SqlXmlNode> FindSqlXmlNode(string nodeName);
        bool SetSqlXmlNodeMetadata(List<SqlXmlColumn> rowData);
        string GetXml();
        string PrintParseTree();
    }

    public class SqlXmlConverter : ISqlXmlConverter
    {
        public SqlXmlNode root { get; private set; }
        public List<SqlXmlNode> table { get; private set; }
        public ParseTree parseTree { get; set; }

        public SqlXmlConverter()
        {
            this.table = new List<SqlXmlNode>();
        }

        public SqlXmlConverter(ParseTree parseTree)
        {
            table = new List<SqlXmlNode>();
            this.parseTree = parseTree;
        }

        public string PrintParseTree()
        {
            return PrintParseTreeNode(0, this.parseTree.Root, new StringBuilder()).ToString();
        }

        private StringBuilder PrintParseTreeNode(int indent, ParseTreeNode node, StringBuilder text)
        {
            if (node == null)
                return text;

            for (int x = 0; x < indent; x++)
                text.Append(" ");

            text.AppendLine(node.ToString() + ": " + node.Term.ToString());
            
            indent += 2;

            foreach (var child in node.ChildNodes)
                PrintParseTreeNode(indent, child, text);

            return text;
        }

        public string GetXml()
        {
            var xml = new XElement("query", new XAttribute("type", "uncompressed"));
            var text = new StringBuilder();

            //add each row from the table
            foreach (var row in table)
            {
                xml.Add(BuildXml(row));
            }

            //compression
            if (xml.Descendants().Attributes("mode").Count() > 0)
            {
                //establish a new working XElement
                var newXml = XElement.Parse(ObjectCopier.Clone(xml.ToString()));

                //group all of the compression nodes together by their sibling's key value
                var nodes = from row in xml.Descendants("row").Descendants()
                            where row.Attributes("mode").Any(a => a.Value.Equals("compression"))
                            group row by row.Parent.Elements().FirstOrDefault().Value into g
                            select new
                            {
                                key = g.Key,
                                xml = g
                            };

                //remove the compression nodes
                newXml.Descendants("row").Descendants().Where(r => r.Attributes("mode").Any(a => a.Value.Equals("compression"))).Remove();

                //remove each row that has more than one occurrence of the key value
                var main = from row in newXml.Descendants("row").Distinct()
                           group row by row.Elements().FirstOrDefault().Value into g
                           select g.ToList().FirstOrDefault();
                
                //loop through the grouping
                foreach (var row in main)
                {
                    //where the key elements match
                    foreach (var x in nodes.Where(k => k.key.Equals(row.Elements().FirstOrDefault().Value)))
                    {
                        //loop through the compressed elements
                        foreach (var i in x.xml)
                        {
                            //and add them to the group
                            row.Add(i);
                        }
                    }
                }

                //set the compressed xml
                xml = new XElement("query", new XAttribute("type", "compressed"), main);
            }

            //add xml declaration
            text.AppendLine("<?xml version=\"1.0\" encoding=\"UTF-8\" ?>");
            text.AppendLine(xml.ToString());

            return text.ToString();
        }

        private string RemoveIllegalXmlCharacters(string xml)
        {
            return xml.Replace("(", string.Empty).Replace(")", string.Empty).Replace("*", string.Empty);
        }

        private XElement BuildXml(SqlXmlNode node)
        {
            if (node == null)
                return null;

            //remove non-xml valid characters
            var element = new XElement(RemoveIllegalXmlCharacters(node.name));

            if (node.siblings.Count > 0)
            {
                foreach (var attr in node.siblings)
                {
                    element.Add(new XAttribute(attr.name, attr.value));
                }
            }

            if (node.children.Count > 0)
            {
                foreach (var child in node.children)
                {
                    element.Add(BuildXml(child));
                }
            }
            else
            {
                //add any metadata to the element
                foreach (var item in node.metadata)
                {
                    element.Add(new XAttribute(item.Key, item.Value));
                }

                element.Value = node.value == null? string.Empty : node.value;
            }

            return element;
        }

        public string WritePattern()
        {
            var previewXml = new XElement("query");
            previewXml.Add(BuildXml(root));

            return previewXml.ToString();
        }

        public bool BuildSqlXmlPattern(ParseTree parseTree)
        {
            bool success = true;

            //build the root
            var root = new SqlXmlNode
            {
                name = "row"
            };

            //grab the root list
            var columnItemList = GetNodeFromParseTree(parseTree.Root, "columnItemList");

            if (columnItemList.Count > 0 /*!= null*/)
            {
                //iterate through the children, finding specific nodes and building the hierarchy
                foreach (var child in columnItemList[0].ChildNodes)
                {
                    var newNode = new SqlXmlNode();

                    //process xmlSqlColumn list
                    if (child.Term.ToString().ToUpper().Contains("XML"))
                    {
                        newNode = ProcessSqlXmlColumn(child);

                        //add the newly created node to the root
                        root.AddChild(newNode);
                    }
                    else //process column items
                    {
                        //check if the column item is an xmlSqlColumn
                        if (GetNodeFromParseTree(child, "XML").Count > 0 /*!= null*/)
                        {
                            newNode = ProcessSqlXmlColumn(GetNodeFromParseTree(child, "XML")[0]);
                            root.AddChild(newNode);
                        }
                        else //it's a normal column item
                        {
                            newNode.name = GetSqlColumnName(child);

                            //if the column item is an attribute
                            if (GetNodeFromParseTree(child, "@").Count > 0 /*[0] != null*/)
                            {
                                //it's a sibling of the root
                                root.AddSibling(newNode);
                            }
                            else //it's a child
                            {
                                //newNode.AddChild(newItemNode);
                                root.AddChild(newNode);
                            }
                        }
                    }
                }
            }

            if (success)
                this.root = root;

            return success;
        }

        private string GetSqlColumnName(ParseTreeNode columnItem)
        {
            //if the columnItem has been aliased
            if (columnItem.ChildNodes.Where(c => c.Term.ToString().ToUpper().Equals("ID")).FirstOrDefault() != null /*.Count > 1*/)
            {
                //if the columnItem is an attribute
                if (GetNodeFromParseTree(columnItem, "@").Count > 0 /*!= null*/)
                {
                    //var attrList = GetNodeFromParseTree(columnItem, "@");
                    return GetNodeFromParseTree(columnItem.ChildNodes[2], "id_simple")[0].ToString().Split(' ')[0];
                }
                else
                {
                    //return the aliased name
                    return GetNodeFromParseTree(columnItem.ChildNodes[1], "id_simple")[0].ToString().Split(' ')[0];
                }
            }
            else
            {
                //grab the column source
                var columnSource = GetNodeFromParseTree(columnItem, "columnSource");

                //if the column source is an aggregate function
                if (columnSource.Count > 0 && GetNodeFromParseTree(columnSource[0], "aggregate").Count > 0 /*!= null*/)
                {
                    var aggregateFunction = GetNodeFromParseTree(columnSource[0], "aggregateName")[0].ChildNodes[0].Term.ToString();
                    var aggregateArg = GetNodeFromParseTree(columnSource[0], "aggregateArg")[0];

                    //if the aggregate argument is not a column name
                    if (GetNodeFromParseTree(aggregateArg, "id_simple").Count == 0 /*== null*/)
                        return string.Format("{0}({1})", aggregateFunction, aggregateArg.ChildNodes[0].Term.ToString());
                    else
                        return string.Format("{0}({1})", aggregateFunction,
                            GetNodeFromParseTree(aggregateArg, "id_simple")[0].ToString().Split(' ')[0]);
                }
                else //not an aggregate
                {
                    var columnSourceIdList =
                        GetNodeFromParseTree(
                            GetNodeFromParseTree(columnItem, "columnSource")[0],
                                "id_simple");

                    return columnSourceIdList.Last().ToString().Split(' ')[0];

                    /*
                    return GetNodeFromParseTree(
                                    GetNodeFromParseTree(columnItem, "columnSource")[0],
                                        "id_simple")[0].ToString().Split(' ')[0];
                     */
                }
            }
        }

        private SqlXmlNode ProcessSqlXmlColumn(ParseTreeNode node)
        {
            var sqlXmlNode = new SqlXmlNode();

            //grab the name of the node
            sqlXmlNode.name = 
                GetNodeFromParseTree(
                    GetNodeFromParseTree(node, "elementName")[0],
                        "id_simple")[0].ToString().Split(' ')[0];

            //if there's a compression based symbol, add a "mode" compression attribute
            if (GetNodeFromParseTree(node, "+").Count > 0)
            {
                sqlXmlNode.AddSibling(new SqlXmlNode
                {
                    name = "mode",
                    value = "compression"
                });
            }

            //if there is a columnItemList within the xmlSqlColumn, process it
            var xmlSqlColumnItemList = GetNodeFromParseTree(node, "columnItemList")[0];

            if (xmlSqlColumnItemList != null)
            {
                //process the column items
                foreach (var xmlSqlColumnItem in xmlSqlColumnItemList.ChildNodes)
                {
                    var newXmlSqlNode = new SqlXmlNode();

                    //if it's a nested xml sql column
                    if (GetNodeFromParseTree(xmlSqlColumnItem, "xml").Count > 0/*!= null*/)
                    //if (xmlSqlColumnItem.Term.ToString().ToUpper().Contains("XML"))
                    {
                        //process the node
                        newXmlSqlNode = ProcessSqlXmlColumn(GetNodeFromParseTree(xmlSqlColumnItem, "xml")[0]);

                        //add as a child (never an attribute)
                        sqlXmlNode.AddChild(newXmlSqlNode);
                    }
                    else
                    {
                        newXmlSqlNode.name = GetSqlColumnName(xmlSqlColumnItem);

                        //if the column item is an attribute
                        if (GetNodeFromParseTree(xmlSqlColumnItem, "@").Count > 0 /*!= null*/)
                        {
                            //it's a sibling
                            sqlXmlNode.AddSibling(newXmlSqlNode);
                        }
                        else //it's a child
                        {
                            sqlXmlNode.AddChild(newXmlSqlNode);
                        }
                    }
                }
            }

            return sqlXmlNode;
        }

        private List<ParseTreeNode> GetNodeFromParseTree(ParseTreeNode node, string matchTerm)
        {
            var nodeList = new List<ParseTreeNode>();
            var queue = new Queue<ParseTreeNode>();
            queue.Enqueue(node);

            while (queue.Count > 0)
            {
                var testNode = queue.Dequeue();

                if (testNode.Term.ToString().ToUpper().Contains(matchTerm.Trim().ToUpper()))
                    nodeList.Add(testNode);

                foreach (var child in testNode.ChildNodes)
                    queue.Enqueue(child);
            }

            return nodeList;
        }

        public List<SqlXmlNode> FindSqlXmlNode(string nodeName)
        {
            var nodeList = new List<SqlXmlNode>();
            var queue = new Queue<SqlXmlNode>();
            queue.Enqueue(this.root);

            while (queue.Count > 0)
            {
                var testNode = queue.Dequeue();

                if (testNode.name.ToUpper().Contains(nodeName.Trim().ToUpper()))
                    nodeList.Add(testNode);

                //search siblings
                foreach (var sibling in testNode.siblings)
                    queue.Enqueue(sibling);

                //search children
                foreach (var child in testNode.children)
                    queue.Enqueue(child);
            }

            return nodeList;
        }

        public bool SetSqlXmlNodeMetadata(List<SqlXmlColumn> rowData)
        {
            bool success = true;

            //clone the pattern
            var row = ObjectCopier.Clone<SqlXmlNode>(root);

            //if the pattern has no nodes or children
            if (root.children.Count <= 0 && root.siblings.Count <= 0)
            {
                //add the row directly as a child
                foreach (var column in rowData)
                {
                    var node = new SqlXmlNode();
                    node.name = column.columnName;
                    node.value = column.value;
                    
                    foreach (var item in column.columnInfo)
                        node.AddMetadata(item.Key, item.Value);

                    row.AddChild(node);
                }
            }
            else
            {
                //recursively add nodes
                foreach (var column in rowData)
                {
                    row = AddSqlXmlNodeMetadata(row, column, false);
                }
            }

            if (success)
                table.Add(row);

            return success;
        }

        private bool IsNodeAggregateFunction(string nodeName)
        {
            //normalize comparison
            var name = nodeName.ToUpper();

            if(name.Contains("AVG") || name.Contains("MIN") || name.Contains("MAX") || name.Contains("STDEV") ||
               name.Contains("STDEVP") || name.Contains("SUM") || name.Contains("VAR") || name.Contains("VARP") ||
               name.Contains("COUNT"))
                return true;
            else
                return false;
        }

        private SqlXmlNode AddSqlXmlNodeMetadata(SqlXmlNode node, SqlXmlColumn column, bool found)
        {
            if (node == null || found == true)
                return node;

            //if the node's name contains an aggregate function or
            //if the node's name matches up to the column name and
            //it hasn't been found already
            if ((IsNodeAggregateFunction(node.name) && string.IsNullOrEmpty(column.columnName) && !found) ||
                (node.name.ToUpper().Equals(column.columnName.ToUpper()) && !found))
            {
                //set the values
                node.value = column.value;
                
                //and the metadata
                foreach (var item in column.columnInfo)
                {
                    node.AddMetadata(item.Key, item.Value);
                }

                found = true;
            }

            //attributes
            foreach (var attribute in node.siblings)
                AddSqlXmlNodeMetadata(attribute, column, found);

            //children
            foreach (var element in node.children)
                AddSqlXmlNodeMetadata(element, column, found);

            return node;
        }
    }

    [Serializable]
    public class SqlXmlNode
    {
        public string name { get; set; }
        public List<SqlXmlNode> siblings { get; private set; }
        public List<SqlXmlNode> children { get; private set; }
        public string value { get; set; }
        public Dictionary<string, string> metadata { get; private set; }

        public SqlXmlNode()
        {
            this.siblings = new List<SqlXmlNode>();
            this.children = new List<SqlXmlNode>();
            this.metadata = new Dictionary<string, string>();
        }

        public void AddSibling(SqlXmlNode sibling)
        {
            if (sibling == null)
                throw new ArgumentNullException("sibling");

            this.siblings.Add(sibling);
        }

        public void AddChild(SqlXmlNode child)
        {
            if (child == null)
                throw new ArgumentNullException("child");

            this.children.Add(child);
        }

        public void AddMetadata(string key, string value)
        {
            if (string.IsNullOrEmpty(key))
                throw new ArgumentNullException("key");

            //if (string.IsNullOrEmpty(value))
            //    throw new ArgumentNullException("value");

            this.metadata.Add(key, string.IsNullOrEmpty(value)? string.Empty : value);
        }
    }

    public static class ObjectCopier
    {
        public static T Clone<T>(T source)
        {
            if (!typeof(T).IsSerializable)
            {
                throw new ArgumentException("The type must be serializable.", "source");
            }

            // Don't serialize a null object, simply return the default for that object
            if (Object.ReferenceEquals(source, null))
            {
                return default(T);
            }

            IFormatter formatter = new BinaryFormatter();
            Stream stream = new MemoryStream();
            using (stream)
            {
                formatter.Serialize(stream, source);
                stream.Seek(0, SeekOrigin.Begin);
                return (T)formatter.Deserialize(stream);
            }
        }
    }   
}
